import { RowDataPacket } from 'mysql2';
import pool from '../config/database';

export interface ICategory extends RowDataPacket {
  id: number;
  name: string;
  description: string;
  created_at: Date;
  updated_at: Date;
}

// 创建分类表
export const createCategoryTable = async (): Promise<void> => {
  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS categories (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) UNIQUE NOT NULL,
      description TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `;

  try {
    await pool.query(createTableSQL);
    console.log('Categories table created or already exists');
  } catch (error) {
    console.error('Error creating categories table:', error);
    throw error;
  }
};

// 分类相关操作
export const Category = {
  // 创建分类
  async create(categoryData: Omit<ICategory, 'id' | 'created_at' | 'updated_at'>): Promise<ICategory> {
    const [result] = await pool.query<ICategory[]>(
      'INSERT INTO categories (name, description) VALUES (?, ?)',
      [categoryData.name, categoryData.description]
    );
    return result[0];
  },

  // 获取所有分类
  async findAll(): Promise<ICategory[]> {
    const [rows] = await pool.query<ICategory[]>(
      'SELECT * FROM categories'
    );
    return rows;
  },

  // 根据ID获取分类
  async findById(id: number): Promise<ICategory | null> {
    const [rows] = await pool.query<ICategory[]>(
      'SELECT * FROM categories WHERE id = ?',
      [id]
    );
    return rows[0] || null;
  },

  // 更新分类
  async update(id: number, data: Partial<ICategory>): Promise<boolean> {
    const [result] = await pool.query(
      'UPDATE categories SET ? WHERE id = ?',
      [data, id]
    );
    return (result as any).affectedRows > 0;
  },

  // 删除分类
  async delete(id: number): Promise<boolean> {
    const [result] = await pool.query(
      'DELETE FROM categories WHERE id = ?',
      [id]
    );
    return (result as any).affectedRows > 0;
  }
}; 